﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace HotelManagement.Class
{
    public class StaffDBManager
    {
        public static int insertStaff(Staff s)
        {
            int rowsInserted = 0;

            try
            {
                SqlConnection conn = ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "INSERT INTO Staff (StaffID, FirstName, LastName, Age, DOB, Gender, Email, BlockNum, StreetName, UnitNum, Country, PostalCode, DutyType, Title, MobileNum, HomeNum,  ReportTo, Notes)" +
                    " VALUES (@staffID, @firstName, @lastName, @age, @dOB, @gender, @email, @blockNum, @streetName, @unitNum, @country, @postalCode, @dutyType, @title, @mobileNum, @homeNum, @reportTo, @notes)";
                comm.Parameters.AddWithValue("@staffID", s.StaffID);
                comm.Parameters.AddWithValue("@firstName", s.FirstName);
                comm.Parameters.AddWithValue("@lastName", s.LastName);
                comm.Parameters.AddWithValue("@age", s.Age);
                comm.Parameters.AddWithValue("@dOB", s.DOB);
                comm.Parameters.AddWithValue("@gender", s.Gender);
                comm.Parameters.AddWithValue("@email", s.Email);
                comm.Parameters.AddWithValue("@blockNum", s.BlockNum);
                comm.Parameters.AddWithValue("@streetName", s.StreetName);
                comm.Parameters.AddWithValue("@unitNum", s.UnitNum);
                comm.Parameters.AddWithValue("@country", s.Country);
                comm.Parameters.AddWithValue("@postalCode", s.PostalCode);
                comm.Parameters.AddWithValue("@dutyType", s.DutyType);
                comm.Parameters.AddWithValue("@title", s.Title);
                comm.Parameters.AddWithValue("@mobileNum", s.MobileNum);
                comm.Parameters.AddWithValue("@homeNum", s.HomeNum);
                comm.Parameters.AddWithValue("@reportTo", s.ReportTo);
                comm.Parameters.AddWithValue("@notes", s.Notes);

                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static ArrayList getAllStaffID()
        {
            ArrayList result = new ArrayList();

            try
            {
                SqlConnection conn = ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT [StaffID] from [Staff] ORDER BY StaffID ASC";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Staff s = new Staff();
                    s.StaffID = (string)dr["StaffID"];

                    result.Add(s);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException E)
            {
                throw E;
            }
            return result;
        }

        public static Staff getStaffById(string id)
        {
            Staff s = null;

            try
            {
                SqlConnection conn = ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@StaffID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    s = new Staff();
                    s.StaffID = (string)dr["StaffID"];
                    s.FirstName = (string)dr["FirstName"];
                    s.LastName = (string)dr["LastName"];
                    s.Age = (int)dr["Age"];
                    s.DOB = (DateTime)dr["DOB"];
                    s.Gender = (string)dr["Gender"];
                    s.Email = (string)dr["Email"];
                    s.BlockNum = (string)dr["BlockNum"];
                    s.StreetName = (string)dr["StreetName"];
                    s.UnitNum = (string)dr["UnitNum"];
                    s.Country = (string)dr["Country"];
                    s.PostalCode = (string)dr["PostalCode"];
                    s.DutyType = (string)dr["DutyType"];
                    s.Title = (string)dr["Title"];
                    s.MobileNum = (int)dr["MobileNum"];
                    s.HomeNum = (int)dr["HomeNum"];
                    s.ReportTo = (string)dr["ReportTo"];
                    s.Notes = (string)dr["Notes"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return s;

        }
        public static Staff getStaffByDutyTypes(string dt)
        {
            Staff result = null;

            try
            {
                SqlConnection conn = ConnectToDatabase();

                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staff WHERE DutyType=@DutyType";
                comm.Parameters.AddWithValue("@DutyType", dt);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    Staff s = new Staff();
                    s.StaffID = (string)dr["StaffID"];
                    s.FirstName = (string)dr["FirstName"];
                    s.LastName = (string)dr["LastName"];
                    s.Age = (int)dr["Age"];
                    s.DOB = (DateTime)dr["DOB"];
                    s.Gender = (string)dr["Gender"];
                    s.Email = (string)dr["Email"];
                    s.BlockNum = (string)dr["BlockNum"];
                    s.StreetName = (string)dr["StreetName"];
                    s.UnitNum = (string)dr["UnitNum"];
                    s.Country = (string)dr["Country"];
                    s.PostalCode = (string)dr["PostalCode"];
                    s.DutyType = (string)dr["DutyType"];
                    s.Title = (string)dr["Title"];
                    s.MobileNum = (int)dr["MobileNum"];
                    s.HomeNum = (int)dr["HomeNum"];
                    s.ReportTo = (string)dr["ReportTo"];
                    s.Notes = (string)dr["Notes"];
                }


                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }
        public static int updateStaff(Staff s)
        {
            int rowsUpdated = 0;


            try
            {
                SqlConnection conn = ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET FirstName=@firstName, LastName=@lastName, Age=@age, DOB=@dOB, Gender=@gender, Email=@email, BlockNum=@blockNum, StreetName=@streetName , UnitNum=@unitNum, Country=@country, PostalCode=@postalCode, DutyType=@dutyType , Title=@title, MobileNum=@mobileNum, HomeNum=@homeNum , ReportTo=@reportTo, Notes=@notes   WHERE StaffID=@staffID";

                comm.Parameters.AddWithValue("@staffID", s.StaffID);
                comm.Parameters.AddWithValue("@firstName", s.FirstName);
                comm.Parameters.AddWithValue("@lastName", s.LastName);
                comm.Parameters.AddWithValue("@age", s.Age);
                comm.Parameters.AddWithValue("@dOB", s.DOB);
                comm.Parameters.AddWithValue("@gender", s.Gender);
                comm.Parameters.AddWithValue("@email", s.Email);
                comm.Parameters.AddWithValue("@blockNum", s.BlockNum);
                comm.Parameters.AddWithValue("@streetName", s.StreetName);
                comm.Parameters.AddWithValue("@unitNum", s.UnitNum);
                comm.Parameters.AddWithValue("@country", s.Country);
                comm.Parameters.AddWithValue("@postalCode", s.PostalCode);
                comm.Parameters.AddWithValue("@dutyType", s.DutyType);
                comm.Parameters.AddWithValue("@title", s.Title);
                comm.Parameters.AddWithValue("@mobileNum", s.MobileNum);
                comm.Parameters.AddWithValue("@homeNum", s.HomeNum);
                comm.Parameters.AddWithValue("@reportTo", s.ReportTo);
                comm.Parameters.AddWithValue("@notes", s.Notes);
                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsUpdated;
        }
        public static int deleteStaff(string staffID)
        {
            int rowsDeleted = 0;


            try
            {
                SqlConnection conn = ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from Staff where StaffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                rowsDeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsDeleted;
        }
        public static SqlConnection ConnectToDatabase()
        {
            SqlConnection Conn = null;
            Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
            return Conn;
        }
    }
}